/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facades;

import entities.Province;
import java.util.Vector;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author damfercer
 */
@Stateless
public class ProvinceFacade extends AbstractFacade<Province> {
    @PersistenceContext(unitName = "ProyectoPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public ProvinceFacade() {
        super(Province.class);
    }
    
    public Vector<Object[]> getAllProvincesByCountryCandidatesAvailability(Integer idCountry){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT province.DESCRIPTION, count(DISTINCT candidate.ID_CANDIDATE) AS contador FROM " +
            "province LEFT JOIN candidate ON " +
            "(candidate.PROVINCE = province.ID_PROVINCE " +
            " OR " +
            " (SELECT EXISTS" +
            " (SELECT * FROM candidate_relocating_info " +
            "  WHERE candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE " +
            "  AND (candidate_relocating_info.PROVINCE=province.ID_PROVINCE " +
            "       OR (candidate_relocating_info.COUNTRY=");
        sb.append(idCountry);
        sb.append(" AND candidate_relocating_info.PROVINCE IS NULL))))) " +
            "LEFT JOIN user ON user.ID_USER=candidate.USER " +
            "WHERE user.ACTIVE=1 " +
            "AND candidate.BUST_BLACK_WHITE IS NOT NULL " +
            "AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL " +
            "AND candidate.BIRTH_DAY IS NOT NULL " +
            "AND province.COUNTRY=");
        sb.append(idCountry);
        sb.append(" group by province.ID_PROVINCE ORDER BY contador DESC");
        Query resultQuery=em.createNativeQuery(sb.toString());
        return (Vector<Object[]>)resultQuery.getResultList();
        
    }
    
    public Vector<Object[]> getAllProvincesByCountryCandidatesResiding(Integer idCountry){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT province.DESCRIPTION, count(DISTINCT candidate.ID_CANDIDATE) AS contador FROM " +
            "province LEFT JOIN candidate ON candidate.PROVINCE = province.ID_PROVINCE " +
            "LEFT JOIN user ON user.ID_USER=candidate.USER " +
            "WHERE user.ACTIVE=1 " +
            "AND candidate.BUST_BLACK_WHITE IS NOT NULL " +
            "AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL " +
            "AND candidate.BIRTH_DAY IS NOT NULL " +
            "AND province.COUNTRY=");
        sb.append(idCountry);
        sb.append(" group by province.ID_PROVINCE ORDER BY contador DESC");
        Query resultQuery=em.createNativeQuery(sb.toString());
        return (Vector<Object[]>)resultQuery.getResultList();
        
    }
}
